using System.Data;
using System.Data.Common;

namespace Util.Database
{
    public sealed class DB
    {
        private DB() { }

        public static void Open(string providerInvariantName, string connectionString)
        {
            DbProviderFactory fact = DbProviderFactories.GetFactory(providerInvariantName);
            _conn = fact.CreateConnection();
            _conn.ConnectionString = connectionString;
            _conn.Open();

            string DBVersion;
            try
            {
                using (IDbCommand cmd = DB.CreateCommand())
                {
                    cmd.CommandText = "SELECT value FROM property WHERE name = 'DB Version'";
                    DBVersion = cmd.ExecuteScalar().ToString();
                }
            }
            catch (DbException)
            {
                DBVersion = DB.Initialize();
            }

            if (DBVersion == "1.00")
            {
                DBVersion = UpdateDBTo110();
            }

            if (DBVersion != "1.10")
            {
                throw new System.FormatException("Invalid DB version and no conversion available.");
            }
            DB.Cleanup();
        }

        public static string UpdateDBTo110()
        {
            throw new System.NotImplementedException("No implementation, because nobody used 1.00");
        }

        public static void Close()
        {
            _conn.Close();
            _conn = null;
        }

        public static bool IsOpen()
        {
            if (_conn != null)
            {
                return _conn.State == ConnectionState.Open;
            }
            return false;
        }

        public static string Initialize()
        {
            using (IDbCommand cmd = DB.CreateCommand())
            {
                cmd.CommandText = "SELECT count(*) FROM sqlite_master WHERE type='table' and upper(name) = 'FEED'";
                if (((long)cmd.ExecuteScalar()) == 0)
                {
                    cmd.CommandText = "CREATE TABLE feed(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT NOT NULL, address TEXT NOT NULL)";
                    cmd.ExecuteNonQuery();
                }

                cmd.CommandText = "SELECT count(*) FROM sqlite_master WHERE type='table' and upper(name) = 'REQUEST'";
                if (((long)cmd.ExecuteScalar()) == 0)
                {
                    cmd.CommandText = "CREATE TABLE request(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, id_show INTEGER NOT NULL, season INTEGER NOT NULL, episode INTEGER NOT NULL)";
                    cmd.ExecuteNonQuery();
                }

                cmd.CommandText = "SELECT count(*) FROM sqlite_master WHERE type='table' and upper(name) = 'SHOW'";
                if (((long)cmd.ExecuteScalar()) == 0)
                {
                    cmd.CommandText = "CREATE TABLE show(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT NOT NULL, namequery TEXT NOT NULL, detailsquery TEXT NOT NULL)";
                    cmd.ExecuteNonQuery();
                }

                cmd.CommandText = "SELECT count(*) FROM sqlite_master WHERE type='table' and upper(name) = 'DOWNLOADED'";
                if (((long)cmd.ExecuteScalar()) == 0)
                {
                    cmd.CommandText = "CREATE TABLE downloaded(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, id_show INTEGER NOT NULL, season INTEGER NOT NULL, episode INTEGER NOT NULL, downloaddate TEXT NOT NULL, downloadaddress TEXT NOT NULL)";
                    cmd.ExecuteNonQuery();
                }

                cmd.CommandText = "SELECT count(*) FROM sqlite_master WHERE type='index' and upper(name) = 'IDX_DOWNLOADED'";
                if (((long)cmd.ExecuteScalar()) == 0)
                {
                    cmd.CommandText = "CREATE INDEX idx_downloaded ON downloaded(id_show, season, episode)";
                    cmd.ExecuteNonQuery();
                }

                cmd.CommandText = "SELECT count(*) FROM sqlite_master WHERE type='table' and upper(name) = 'PROPERTY'";
                if (((long)cmd.ExecuteScalar()) == 0)
                {
                    cmd.CommandText = "CREATE TABLE property(name TEXT NOT NULL, value TEXT NOT NULL)";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "INSERT INTO property(name, value) VALUES ('DB Version', '1.00')";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "INSERT INTO property(name, value) VALUES ('NZB Download Folder', 'c:\')";
                    cmd.ExecuteNonQuery();
                }
            }
            return "1.10";
        }

        public static void Cleanup()
        {
/*            using (IDbCommand cmd = DB.CreateCommand())
            {
                cmd.CommandText = "delete from request where rowid in (select request.rowid from request, downloaded WHERE downloaded.season <> 0 AND downloaded.episode <> 0 and request.id_show = downloaded.id_show and request.season = downloaded.season and request.episode = downloaded.episode)";
                cmd.ExecuteNonQuery();
            }*/
        }

        public static IDbCommand CreateCommand()
        {
            return _conn.CreateCommand();
        }

        
        private static DbConnection _conn;
    }
}
